/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package sys.dao;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import sys.bean.CaixaBean;
import sys.bean.ClienteBean;
import sys.bean.PedidoBean;
import sys.bean.UsuarioBean;
import sys.bean.ValeMercadoriaBean;
import sys.utils.ConexaoADS;
import sys.utils.Utils;
import static sys.utils.Utils.dataCorreta;

/**
 *
 * @author Administrador
 */
public class ValeMercadoriaDao {

    /*
     total do valor do vale (venda) - utilizado para rentabilidade
     */
    public static List<ValeMercadoriaBean> totalVendaValeEmpresaDiario(String dataInicial, String dataFinal) throws SQLException, IOException {
        List<ValeMercadoriaBean> lista = new ArrayList<>();
        Connection con = ConexaoADS.getConection();

        String sql = "SELECT C13DATA, SUM(C28VALOR) AS VALOR \n"
                + "FROM SPAC28 \n"
                + "JOIN SPAC13 ON (C13NPED = C28PEDDEV) \n"
                + "AND C13DATA BETWEEN '" + dataInicial + "' AND '" + dataFinal + "' \n"
                + "AND C28PEDDEV <> '' \n"
                + "GROUP BY 1";
        System.out.println(sql);

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {

            ValeMercadoriaBean vale = new ValeMercadoriaBean();

            vale.setvVale(rs.getDouble("VALOR"));

            PedidoBean pedido = new PedidoBean();
            pedido.setData(dataCorreta(rs.getString("C13DATA")));

            vale.setPedido(pedido);

            lista.add(vale);
        }

        stmt.close();
        con.close();
        return lista;
    }

    /*
     total do valor do vale (venda) - utilizado para rentabilidade
     */
    public static List<ValeMercadoriaBean> totalVendaValeEmpresaMensal(String dataInicial, String dataFinal) throws SQLException, IOException {
        List<ValeMercadoriaBean> lista = new ArrayList<>();
        Connection con = ConexaoADS.getConection();

        String sql = "SELECT SUBSTRING(C13DATA,5,2) AS MES, SUBSTRING(C13DATA,1,4) AS ANO,SUM(C28VALOR) AS VALOR \n"
                + "FROM SPAC28 \n"
                + "JOIN SPAC13 ON (C13NPED = C28PEDDEV) \n"
                + "AND C13DATA BETWEEN '" + dataInicial + "' AND '" + dataFinal + "' \n"
                + "AND C28PEDDEV <> '' \n"
                + "GROUP BY 1,2";
        System.out.println(sql);

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {

            ValeMercadoriaBean vale = new ValeMercadoriaBean();

            vale.setvVale(rs.getDouble("VALOR"));

            PedidoBean pedido = new PedidoBean();
            pedido.setMes(rs.getString("MES"));
            pedido.setAno(rs.getString("ANO"));

            vale.setPedido(pedido);

            lista.add(vale);
        }

        stmt.close();
        con.close();
        return lista;
    }

    /*
     total do valor do vale (venda) - utilizado para rentabilidade
     */
    public static List<ValeMercadoriaBean> totalVendaValeMensal(String dataInicial, String dataFinal, String vendedor) throws SQLException, IOException {
        List<ValeMercadoriaBean> lista = new ArrayList<>();
        Connection con = ConexaoADS.getConection();

        String sql = "SELECT SUBSTRING(C13DATA,5,2) AS MES, USUNOME, SUM(C28VALOR) AS VALOR \n"
                + "FROM SPAC28 \n"
                + "JOIN SPAC13 ON (C13NPED = C28PEDDEV) \n"
                + "JOIN SPUSU ON (C28VEND = USUCODIGO) \n"
                + "JOIN SPFUSU ON (USUFUNCAO = FUCODIGO) \n"
                + "AND C13DATA BETWEEN '" + dataInicial + "' AND '" + dataFinal + "' \n"
                + "AND C28PEDDEV <> '' \n"
                + "AND C28VEND LIKE '%" + vendedor + "%' \n"
                + "GROUP BY 1,2";

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {

            ValeMercadoriaBean vale = new ValeMercadoriaBean();

            vale.setvVale(rs.getDouble("VALOR"));

            UsuarioBean usuario = new UsuarioBean();
            usuario.setNome(rs.getString("USUNOME"));

            vale.setUsuario(usuario);

            PedidoBean pedido = new PedidoBean();
            pedido.setData(rs.getString("MES"));

            vale.setPedido(pedido);

            lista.add(vale);
        }

        stmt.close();
        con.close();
        return lista;
    }

    /*
     total do valor do vale (venda) - utilizado para rentabilidade
     */
    public static List<ValeMercadoriaBean> totalVendaValeDiario(String dataInicial, String dataFinal, String vendedor) throws SQLException, IOException {
        List<ValeMercadoriaBean> lista = new ArrayList<>();
        Connection con = ConexaoADS.getConection();

        String sql = "SELECT C13DATA, C04NOME, SUM(C28VALOR) AS VALE \n"
                + "FROM SPAC28 \n"
                + "JOIN SPAC13 ON (C13NPED = C28PEDDEV) \n"
                + "JOIN SPAC04 ON (C13VEND = C04CODIGO)\n"
                + "WHERE C28DTEMIS BETWEEN '" + dataInicial + "' AND '" + dataFinal + "' \n"
                + "AND C28PEDDEV <> '' \n"
                + "AND C04CODIGO LIKE '%" + vendedor + "%' \n"
                + "GROUP BY 1,2";

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {

            ValeMercadoriaBean vale = new ValeMercadoriaBean();

            vale.setvVale(rs.getDouble("VALE"));

            UsuarioBean usuario = new UsuarioBean();
            usuario.setNome(rs.getString("C04NOME"));

            vale.setUsuario(usuario);

            PedidoBean pedido = new PedidoBean();
            pedido.setData(dataCorreta(rs.getString("C13DATA")));

            vale.setPedido(pedido);

            lista.add(vale);
        }

        stmt.close();
        con.close();
        return lista;
    }

    /*
     total do valor do vale (venda) - utilizado para rentabilidade
     */
    public static List<ValeMercadoriaBean> totalVendaVale(String dataInicial, String dataFinal, String vendedor) throws SQLException, IOException {
        List<ValeMercadoriaBean> lista = new ArrayList<>();
        Connection con = ConexaoADS.getConection();

        String sql = "SELECT C04NOME, SUM(C28VALOR) AS VALE \n"
                + "FROM SPAC28 \n"
                + "JOIN SPAC13 ON (C13NPED = C28PEDDEV) \n"
                + "JOIN SPAC04 ON (C13VEND = C04CODIGO)\n"
                + "WHERE C28DTEMIS BETWEEN '"+dataInicial+"' AND '"+dataFinal+"' \n"
                + "AND C28PEDDEV <> '' \n"
                + "AND C13VEND LIKE '%"+vendedor+"%' \n"
                + "GROUP BY 1";
       
          PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {

            ValeMercadoriaBean vale = new ValeMercadoriaBean();

            vale.setvVale(rs.getDouble("VALE"));

            UsuarioBean usuario = new UsuarioBean();
            usuario.setNome(rs.getString("C04NOME"));

            vale.setUsuario(usuario);

            lista.add(vale);
        }

        stmt.close();
        con.close();
        return lista;
    }


    /*
     alterando dados do vale mercadoria - TRANSAÇÃO
     */
    public static void alteraTransacaoVM(String dataTabela, double valor, String transacao) throws SQLException, IOException {

        Connection con = ConexaoADS.getConection();

        String sql = "UPDATE TC" + dataTabela + " SET TCVLR = ? WHERE TCNTRA = ?";

        PreparedStatement stmt = con.prepareStatement(sql);

        stmt.setDouble(1, valor);
        stmt.setString(2, transacao);

        stmt.execute();
        stmt.close();

        con.close();

    }

    /*
     alterando dados do vale mercadoria - VALOR
     */
    public static void alteraValorVM(double valor, String vale) throws SQLException, IOException {

        Connection con = ConexaoADS.getConection();

        String sql = "UPDATE SPAC28 SET C28VALOR = ? WHERE C28CODIGO = ?";

        PreparedStatement stmt = con.prepareStatement(sql);

        stmt.setDouble(1, valor);
        stmt.setString(2, vale);

        stmt.execute();
        stmt.close();

        con.close();

    }
    /*
     alterando dados do vale mercadoria - CLIENTE
     */

    public static void alteraClienteVM(String codCliente, String vale) throws SQLException, IOException {

        Connection con = ConexaoADS.getConection();

        String sql = "UPDATE SPAC28 SET C28CLIE = ? WHERE C28CODIGO = ?";

        PreparedStatement stmt = con.prepareStatement(sql);

        stmt.setString(1, codCliente);
        stmt.setString(2, vale);

        stmt.execute();
        stmt.close();

        con.close();

    }

    /*
     retornando total de haver do cliente
     */
    public static double totalHaverCliente(String idCliente) throws SQLException, IOException {
        double total = 0;

        Connection con = ConexaoADS.getConection();

        String sql = "SELECT SUM(C28VALOR) AS TOTAL FROM SPAC28 \n"
                + "WHERE C28CLIE = '" + idCliente + "' \n"
                + "AND C28BAIXA <> 'C'";

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            total = rs.getDouble("TOTAL");
        }

        stmt.close();
        con.close();

        return total;
    }

    public static List<ValeMercadoriaBean> listaVales(String dataInicial, String dataFinal, String vendedor, String codCliente, String codVale) throws SQLException, IOException {
        List<ValeMercadoriaBean> lista = new ArrayList<>();
        Connection con = ConexaoADS.getConection();

        String sql = "SELECT C28CODIGO, C28DTEMIS, C04NOME,  C28CLIE, C05NOME, C28VALOR,  C28VRBAIXA, C28DTBAIXA, C28PEDORI, C28VINCULO,  C28NRTRAN \n"
                + "FROM SPAC28 \n"
                + "INNER JOIN SPAC04 ON (C04CODIGO = C28VEND) \n"
                + "INNER JOIN SPAC05 ON (C05CODIGO = C28CLIE) \n"
                + "WHERE C28DTEMIS BETWEEN '" + dataInicial + "' AND '" + dataFinal + "'\n"
                + "AND C28VEND LIKE '%" + vendedor + "%'\n"
                + "AND C28CLIE LIKE '%" + codCliente + "%'\n"
                + "AND C28CODIGO LIKE '%" + codVale + "%' \n";

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {

            ValeMercadoriaBean vale = new ValeMercadoriaBean();

            if (!rs.getString("C28VINCULO").equals("")) {
                vale.setnVale(rs.getString("C28CODIGO"));
                vale.setDataEmissao(dataCorreta(rs.getString("C28DTEMIS")));

                UsuarioBean usuario = new UsuarioBean();
                usuario.setNome(rs.getString("C04NOME"));

                PedidoBean pedido = new PedidoBean();
                pedido.setUsuario(usuario);

                ClienteBean cliente = new ClienteBean();
                cliente.setCodigo(rs.getString("C28CLIE"));
                cliente.setNome(rs.getString("C05NOME"));

                pedido.setCliente(cliente);

                vale.setPedido(pedido);
                vale.setvVale(rs.getDouble("C28VALOR"));
                vale.setvBaixa(rs.getDouble("C28VRBAIXA"));
                vale.setDataBaixa(dataCorreta(rs.getString("C28DTBAIXA")));

                vale.setPedidoOrigem("(" + rs.getString("C28VINCULO") + ") " + rs.getString("C28PEDORI"));

                vale.setVinculo(rs.getString("C28VINCULO"));
                vale.setnTransacao(rs.getString("C28NRTRAN"));

                lista.add(vale);
            } else {
                vale.setnVale(rs.getString("C28CODIGO"));
                vale.setDataEmissao(dataCorreta(rs.getString("C28DTEMIS")));

                UsuarioBean usuario = new UsuarioBean();
                usuario.setNome(rs.getString("C04NOME"));

                PedidoBean pedido = new PedidoBean();
                pedido.setUsuario(usuario);

                ClienteBean cliente = new ClienteBean();
                cliente.setCodigo(rs.getString("C28CLIE"));
                cliente.setNome(rs.getString("C05NOME"));

                pedido.setCliente(cliente);

                vale.setPedido(pedido);
                vale.setvVale(rs.getDouble("C28VALOR"));
                vale.setvBaixa(rs.getDouble("C28VRBAIXA"));
                vale.setDataBaixa(dataCorreta(rs.getString("C28DTBAIXA")));

                if (rs.getString("C28PEDORI").equals("")) {
                    vale.setPedidoOrigem("'VALE AVULSO'");
                } else {
                    vale.setPedidoOrigem(rs.getString("C28PEDORI"));
                }

                vale.setVinculo(rs.getString("C28VINCULO"));
                vale.setnTransacao(rs.getString("C28NRTRAN"));

                lista.add(vale);
            }
        }

        stmt.close();
        con.close();
        return lista;
    }

    public static ValeMercadoriaBean DadosVale(String codVale, String dataTabela) throws SQLException, IOException {

        Connection con = ConexaoADS.getConection();

        String sql = "SELECT C28CODIGO, C04CODIGO, C04NOME, TCNTER, TCHORA, C28DTEMIS, C28NRTRAN, C28VALOR, C28VINCULO, C05CODIGO, C05NOME, C28PEDORI, C28PEDDEV \n"
                + "FROM SPAC28 \n"
                + "INNER JOIN TC" + dataTabela + " ON (TCNTRA = C28NRTRAN) \n"
                + "INNER JOIN SPAC04 ON (C04CODIGO = C28VEND) \n"
                + "INNER JOIN SPAC05 ON (C05CODIGO = C28CLIE) \n"
                + "WHERE C28CODIGO = '" + codVale + "'";

        PreparedStatement stmt = con.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        ValeMercadoriaBean vale = new ValeMercadoriaBean();

        while (rs.next()) {

            vale.setnVale(rs.getString("C28CODIGO"));

            UsuarioBean usuario = new UsuarioBean();
            usuario.setCodigo(rs.getString("C04CODIGO"));
            usuario.setNome(rs.getString("C04NOME"));
            vale.setUsuario(usuario);

            vale.setDataEmissao(dataCorreta(rs.getString("C28DTEMIS")));
            vale.setnTransacao(rs.getString("C28NRTRAN"));
            vale.setvVale(rs.getDouble("C28VALOR"));
            vale.setVinculo(rs.getString("C28VINCULO"));
            vale.setPedidoOrigem(rs.getString("C28PEDORI"));
            vale.setPedidoGeradoDevolucao(rs.getString("C28PEDDEV"));

            ClienteBean cliente = new ClienteBean();
            cliente.setCodigo(rs.getString("C05CODIGO"));
            cliente.setNome(rs.getString("C05NOME"));
            vale.setCliente(cliente);

            CaixaBean caixa = new CaixaBean();
            caixa.setHora(Utils.horaCorreta(rs.getString("TCHORA")));
            caixa.setTerminal(rs.getString("TCNTER"));
            vale.setCaixa(caixa);

        }

        stmt.close();
        con.close();

        return vale;
    }
}
